package com.dw.widget.slideshow.utils.db;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * 数据库工具类
 * @author DaiYu
 *
 */
public class DataBaseHelper extends SQLiteOpenHelper{

	private final String ID_COLUMN = "_id";
	
	private String[] dataColumn;    // 数据列
	private String dbName;          // 数据库名
	private String tableName;       // 表名
	
	private Class<? extends DataObject> dataClass;       // 数据实体类型
	
	public final String[] getDataColumn() {
		return dataColumn;
	}

	public final String getDbName() {
		return dbName;
	}

	public final void setDataColumn(String[] dataColumn) {
		this.dataColumn = dataColumn;
	}

	public final void setDbName(String dbName) {
		this.dbName = dbName;
	}

	public final String getTableName() {
		return tableName;
	}

	public final void setTableName(String tableName) {
		this.tableName = tableName;
	}

	private DataBaseHelper(Context context, String name, CursorFactory factory,
			int version, String[] columns) {
		super(context, name, factory, version);
		
		dataColumn = columns;
		dbName = name;
		if(dataClass != null)
		{
			tableName = dataClass.getName();
		}
	}
	
	public DataBaseHelper(Context context, String db, Class<? extends DataObject> c) {

		this(context, db, null, 1, null);
		
		dataClass = c;
		
		// 表名默认为类名
		if(dataClass != null)
		{
			tableName = dataClass.getSimpleName();
		}
		
		List<String> cols = new ArrayList<String>();
		Field[] fields = c.getDeclaredFields();
		for(Field f : fields)
		{
			cols.add(f.getName());
		}
		
		dataColumn = cols.toArray(new String[cols.size()]);
		
		createTable();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		
		
	}
	
	public void createTable()
	{
		SQLiteDatabase db = getWritableDatabase();
		
		if(dataColumn == null || dataColumn.length <= 0 || tableName == null || "".equals(tableName))
		{
			return;
		}
		
		String column = "%1$s text";
		String columns = String.format("%1$s integer primary key autoincrement", ID_COLUMN);
		
		int colCount = dataColumn.length;
		for(int i = 0; i < colCount; i++)
		{
			columns += String.format("," + column, dataColumn[i]);
		}
		
		String sqlCreate = String.format("create table if not exists %1$s(%2$s)", tableName, columns);
		db.execSQL(sqlCreate);
		
		db.close();
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		
	}

	public long insert(DataObject data)
	{
		if(data == null)
		{
			return -1;
		}
		
		SQLiteDatabase db = getWritableDatabase();
		
		ContentValues values = getValues(data);
		
		long id = db.insert(tableName, null, values);
		data.setId(id);
		
		db.close();
		
		return id;
	}
	
	/**
	 * 通过ID更新
	 * @param value
	 * @return
	 */
	public int update(DataObject value)
	{
		if(value == null)
		{
			return -1;
		}
		
		String strCondition = "";
		
		// 构造条件语句
		long id = -1;
		
		Class<? extends DataObject> c = value.getClass();
		
		Field field = getIdField(c);
		field.setAccessible(true);
		
		try {
			id = field.getLong(value);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(id >= 0)
		{
			strCondition += String.format("%1$s='%2$s'", ID_COLUMN, String.valueOf(id));
		}
		
		return update(strCondition, value);
	}
	
	/**
	 * 
	 * @param condition
	 * @param value
	 * @return
	 */
	public int update(DataObject condition, DataObject value)
	{
		if(value == null)
		{
			return -1;
		}
		
		String strCondition = getConditionString(condition);
		return update(strCondition, value);
	}
	
	/**
	 * 根据更新语句更新数据库
	 * @param condition
	 * @param value
	 * @return
	 */
	private int update(String condition, DataObject value)
	{
		if(value == null)
		{
			return -1;
		}
		
		// 构造设置语句
		ContentValues values = getValues(value);
		
		SQLiteDatabase db = getWritableDatabase();
		int rows = db.update(tableName, values, "".equals(condition) ? null : condition, null);
		
		db.close();
		
		return rows;
	}
	
	/**
	 * 删除数据库
	 * @param condition
	 * @return
	 */
	public int delete(DataObject condition)
	{
		String strCondition = getConditionString(condition);
		SQLiteDatabase db = getWritableDatabase();
		int rows = db.delete(tableName, strCondition, null);
		
		db.close();
		
		return rows;
	}
	
	/**
	 * 数据库查询
	 * @param condition
	 * @return
	 */
	public List<DataObject> query(DataObject condition)
	{
		String strCondition = getConditionString(condition);
		
		SQLiteDatabase db = getReadableDatabase();
		
		List<String> cols = new ArrayList<String>();
		cols.add(ID_COLUMN);
		cols.addAll(Arrays.asList(dataColumn));
		Cursor cursor = db.query(tableName, cols.toArray(new String[dataColumn.length + 1]), 
				"".equals(strCondition) ? null : strCondition, null, null, null, ID_COLUMN);
		
		List<DataObject> items = new ArrayList<DataObject>();
		
		Class<? extends DataObject> c = dataClass;
		while(cursor.moveToNext())
		{
			try
			{
				DataObject item = (DataObject)(c.newInstance());
				item.setDbHelper(this);
	
				// id
				long id = cursor.getLong(0);
				Class<? extends DataObject> cla = item.getClass();
				Field fieldId = getIdField(cla);
				fieldId.setAccessible(true);
				fieldId.setLong(item, id);
				
				// 数据列
				int count = dataColumn.length;
				for(int i = 0; i < count; i++)
				{
					setParameter(item, dataColumn[i], cursor.getString(i + 1));
				}
				
				items.add(item);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		cursor.close();
		db.close();
		
		return items;
	}
	
	/**
	 * <p>Description: 分页查询</p>
	 * @param condition 查询条件
	 * @param page 页数
	 * @param number 每页条数
	 */
	public List<DataObject> queryPage(DataObject condition, int page, int number)
	{
		String strCondition = getConditionString(condition);
		if(strCondition != null && "".equals(strCondition) == false)
		{
			strCondition = "WHERE " + strCondition;
		}
		else
		{
			strCondition = "";
		}
		
		StringBuilder cols = new StringBuilder();
		cols.append(ID_COLUMN);
		for(String col : dataColumn)
		{
			cols.append(",");
			cols.append(col);
		}
		String sqlQuery = String
				.format("SELECT %1$s FROM %2$s %3$s ORDER BY %4$s DESC LIMIT (%5$d*(%6$s-1)), %5$d",
						cols.toString(), tableName, strCondition, ID_COLUMN, number, page);
		List<DataObject> items = query(sqlQuery);
		
		return items;
	}
	
	/**
	 * 带后缀的sql查询
	 * @param condition
	 * @param suffix
	 * 查询语句后缀
	 * @return
	 */
	public List<DataObject> query(DataObject condition, String suffix)
	{
		String strCondition = getConditionString(condition);
		if(strCondition != null && "".equals(strCondition) == false)
		{
			strCondition = "WHERE " + strCondition;
		}
		else
		{
			strCondition = "";
		}
		
		StringBuilder cols = new StringBuilder();
		cols.append(ID_COLUMN);
		for(String col : dataColumn)
		{
			cols.append(",");
			cols.append(col);
		}
		String sqlQuery = String
				.format("SELECT %1$s FROM %2$s %3$s %4$s",
						cols.toString(), tableName, strCondition, suffix);
		List<DataObject> items = query(sqlQuery);
		
		return items;
	}
	
	public List<DataObject> query(String sql)
	{
		if(sql == null || "".equals(sql))
		{
			return null;
		}
		
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery(sql, null);
		
		List<DataObject> items = new ArrayList<DataObject>();
		
		Class<? extends DataObject> c = dataClass;
		while(cursor.moveToNext())
		{
			try
			{
				DataObject item = (DataObject)(c.newInstance());
				item.setDbHelper(this);
	
				// id
				long id = cursor.getLong(0);
				Class<? extends DataObject> cla = item.getClass();
				Field fieldId = getIdField(cla);
				fieldId.setAccessible(true);
				fieldId.setLong(item, id);
				
				// 数据列
				int count = dataColumn.length;
				for(int i = 0; i < count; i++)
				{
					setParameter(item, dataColumn[i], cursor.getString(i + 1));
				}
				
				items.add(item);
			}
			catch(Exception e)
			{
				e.printStackTrace();
			}
		}
		
		cursor.close();
		db.close();
		
		return items;
	}
	
	/**
	 * 递归的从基类DataObject中找到_id字段
	 * @param c
	 * @return
	 */
	private Field getIdField(Class<? extends DataObject> c)
	{
		Class<?> cla = c;
		
		// 递归的从基类DataObject中找到_id字段
		Field field = null;
		while(field == null)
		{
			try
			{
				field = cla.getDeclaredField(ID_COLUMN);
			}
			catch(Exception ex)
			{
				cla = cla.getSuperclass();
			}
		}
		
		return field;
	}
	
	private String getConditionString(DataObject condition)
	{
		if(condition == null)
		{
			return "";
		}
		
		String strCondition = "";
		
		// 构造条件语句
		long id = -1;
		
		Class<? extends DataObject> c = condition.getClass();
		
		Field field = getIdField(c);
		field.setAccessible(true);
		
		try {
			id = field.getLong(condition);
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		if(id >= 0)
		{
			strCondition += String.format("%1$s='%2$s'", ID_COLUMN, String.valueOf(id));
		}
		
		// 其他数据字段条件
		int count = dataColumn.length;
		for(int i = 0; i < count; i++)
		{
			String param = getParameter(condition, dataColumn[i]);
			if(param != null && "".equals(param) == false)
			{
				if(strCondition != null && "".equals(strCondition) == false)
				{
					strCondition += " and ";
				}
				
				strCondition += String.format("%1$s='%2$s'", dataColumn[i], param);
			}
		}
		
		return strCondition;
	}
	
	private ContentValues getValues(DataObject data)
	{
		ContentValues values = new ContentValues();
		
		int colCount = dataColumn.length;
		for(int i = 0; i < colCount; i++)
		{
			String name = dataColumn[i];
			String value = getParameter(data, name);
			if(value != null)
			{
				values.put(name, value);
			}
		}
		
		return values;
	}
	
	/**
	 * 通过反正设置实体对象中的值
	 * @param data
	 * @param name
	 * @param value
	 */
	private void setParameter(DataObject data, String name, String value)
	{
		try
		{
			Class<? extends DataObject> c = data.getClass();
			Field field = c.getDeclaredField(name);
			field.setAccessible(true);
			field.set(data, value);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
	
	/**
	 * 通过反射从实体对象中获取值
	 * @param data
	 * @param name
	 * @return
	 */
	private String getParameter(DataObject data, String name)
	{
		try
		{
			Class<? extends DataObject> c = data.getClass();
			Field field = c.getDeclaredField(name);
			field.setAccessible(true);
			return (String)field.get(data);
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
		
		return null;
	}
	
	/**
	 * 清除数据库中所有数据
	 */
	public void clear()
	{
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL(String.format("delete from %1$s", tableName));
	}
}
